import pymysql
import pandas as pd
from config.env import DataBaseConfig
from sqlalchemy import create_engine, NullPool
from urllib.parse import quote_plus as urlquote

class MySQLHelper:
    def __init__(self):
        self.uri = (
                       "mysql+pymysql://{}:%s@{}:{}/{}?charset=utf8".format(
                           DataBaseConfig.db_username,
                           DataBaseConfig.db_host,
                           DataBaseConfig.db_port,
                           DataBaseConfig.db_database)
                   ) % urlquote(DataBaseConfig.db_password)
        self.engine = create_engine(self.uri, echo=False, poolclass=NullPool)

    def df_to_table(self, df, table_name, if_exists, dtype_dict):
        df.to_sql(name=table_name, con=self.engine, if_exists=if_exists,
                  index=False,
                  dtype=dtype_dict,
                  chunksize=1000)

    def table_to_df(self, sql):
        df = pd.read_sql(sql, con=self.engine)
        return df